package com.union.core.complain.dao.impl;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.hnjowe.webdb.DBOperation;
import com.union.core.complain.dao.ComplainDao;
import com.union.core.complain.model.Complain;

public class ComplainDaoImpl implements ComplainDao{
	
	private static Logger log = Logger.getLogger(ComplainDaoImpl.class.getName());
	private DBOperation opera;
	private Complain complain;
	
	public ComplainDaoImpl(){
		
	}
	
	public List<Complain> queryList(int complain_id,String complain_name,String complain_content,int organization_id,String organization_name,String join_date1,
			String join_date2,String complain_phone,String add_time,int ishandle,String paramName, String paramValue, String orderByName,int start,int pageSize){
		String sqlstr1 = ""; 	//条件1
		String sqlstr2 = ""; 	//条件2
		String sqlstr3 = ""; 	//条件3
		String sqlstr4 = ""; 	//条件4
		String sqlstr5 = ""; 	//条件5
		String sqlstr6 = ""; 	//条件6
		String sqlstr7 = ""; 	//条件7
		String sqlstr8 = ""; 	//条件8
		String sqlstr9 = ""; 	//条件9
		String sqlstr10 = ""; 	//条件10
		String orderbySql = ""; //排序条件
		
		if (complain_id!=0) sqlstr1 = " and complain_id="+complain_id;
		if (complain_name!=null) sqlstr2 = " and complain_name='"+complain_name+"'";
		if (complain_content!=null) sqlstr3 = " and complain_content='"+complain_content+"'";
		if (complain_phone!=null) sqlstr4 = " and complain_phone='"+complain_phone+"'";
		if (add_time!=null) sqlstr5 = " and add_time='"+add_time+"'";
		if (join_date1 != null && join_date2 != null){
			sqlstr6 = " and add_time between DATE_FORMAT('"+join_date1+"','%Y-%m-%d') and DATE_FORMAT('"+join_date2+"','%Y-%m-%d') ";
		}
		if (organization_id!=0) sqlstr8 = " and organization_id="+organization_id;
		if (organization_name!=null) sqlstr9 = " and organization_name='"+organization_name+"'";
		if (ishandle!=0) sqlstr10 = " and ishandle="+ishandle;
		
		if (paramName != null && paramValue != null) {
			if(paramName.equals("complain_id")) sqlstr7 = " and complain_id = "+paramValue;
			if(paramName.equals("complain_name")) sqlstr7 = " and complain_name like '%"+paramValue+"%'";
			if(paramName.equals("complain_content")) sqlstr7 = " and complain_content like '%"+paramValue+"%'";
			if(paramName.equals("complain_phone")) sqlstr7 = " and complain_phone like '%"+complain_phone+"%'";
			if(paramName.equals("add_time")) sqlstr7 = " and add_time like '%"+paramValue+"%'";
			if(paramName.equals("organization_id")) sqlstr7 = " and organization_id = "+paramValue;
			if(paramName.equals("organization_name")) sqlstr7 = " and organization_name like '%"+paramValue+"%'";
		}

		if (orderByName != null&& !"".equals(orderByName)) {
			String str[] = orderByName.split("-");			
			if (str[1].equals("UP"))
				orderbySql = " order by " + str[0];
			if (str[1].equals("DOWN"))
				orderbySql = " order by " + str[0] + " desc";
		} else {
			orderbySql = " order by complain_id asc";
		}
		
		List<Complain> complainList = new ArrayList<Complain>();
		
		try {
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "select "
					+ "complain_id,complain_name,complain_phone,organization_id,organization_name,complain_content," 
					+ "add_time,handle_user,handle_suggestion,hundle_time,ishandle,mobile,unit,address,email" 
					+ " from complain where complain_id>0" + sqlstr1 + sqlstr2 + sqlstr3 + sqlstr4 + sqlstr5+ sqlstr6 
					+ sqlstr7 + sqlstr8 + sqlstr9 + sqlstr10 +orderbySql  
			        + " LIMIT ?,?";
			opera.prepStatment(sql);
			 opera.setInt(1, start);
			 opera.setInt(2, pageSize);
			ResultSet res = opera.executeQuery();
			int i = 0;
			while (res.next()) {
				complain = new Complain();
				complain.setComplain_id(res.getInt(1));
				complain.setComplain_name(res.getString(2));
				complain.setComplain_phone(res.getString(3));
				complain.setOrganization_id(res.getInt(4));
				complain.setOrganization_name(res.getString(5));
				complain.setComplain_content(res.getString(6));

				complain.setAdd_time(res.getString(7));
				complain.setHandle_user(res.getString(8));
				complain.setHandle_suggestion(res.getString(9));
				complain.setHundle_time(res.getString(10));
				complain.setIshandle(res.getInt(11));
				complain.setMobile(res.getString(12));
				complain.setUnit(res.getString(13));
				complain.setAddress(res.getString(14));
				complain.setEmail(res.getString(15));
				
				complainList.add(i, complain);
				i++;
			}
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}	
		return complainList;
	}
	
	public int quertListCount(int complain_id,String complain_name,String complain_content,int organization_id,String organization_name,String join_date1,
			String join_date2,String complain_phone,String add_time,int ishandle,String paramName, String paramValue){
		String sqlstr1 = ""; 	//条件1
		String sqlstr2 = ""; 	//条件2
		String sqlstr3 = ""; 	//条件3
		String sqlstr4 = ""; 	//条件4
		String sqlstr5 = ""; 	//条件5
		String sqlstr6 = ""; 	//条件6
		String sqlstr7 = ""; 	//条件7
		String sqlstr8 = ""; 	//条件8
		String sqlstr9 = ""; 	//条件9
		String sqlstr10 = ""; 	//条件10
		
		if (complain_id!=0) sqlstr1 = " and complain_id="+complain_id;
		if (complain_name!=null) sqlstr2 = " and complain_name='"+complain_name+"'";
		if (complain_content!=null) sqlstr3 = " and complain_content='"+complain_content+"'";
		if (complain_phone!=null) sqlstr4 = " and complain_phone='"+complain_phone+"'";
		if (add_time!=null) sqlstr5 = " and add_time='"+add_time+"'";
		if (join_date1 != null && join_date2 != null){
			sqlstr6 = " and add_time between DATE_FORMAT('"+join_date1+"','%Y-%m-%d') and DATE_FORMAT('"+join_date2+"','%Y-%m-%d') ";
		}
		if (organization_id!=0) sqlstr8 = " and organization_id="+organization_id;
		if (organization_name!=null) sqlstr9 = " and organization_name='"+organization_name+"'";
		if (ishandle!=0) sqlstr10 = " and ishandle="+ishandle;
		
		if (paramName != null && paramValue != null) {
			if(paramName.equals("complain_id")) sqlstr7 = " and complain_id = "+paramValue;
			if(paramName.equals("complain_name")) sqlstr7 = " and complain_name like '%"+paramValue+"%'";
			if(paramName.equals("complain_content")) sqlstr7 = " and complain_content like '%"+paramValue+"%'";
			if(paramName.equals("complain_phone")) sqlstr7 = " and complain_phone like '%"+complain_phone+"%'";
			if(paramName.equals("add_time")) sqlstr7 = " and add_time like '%"+paramValue+"%'";
			if(paramName.equals("organization_id")) sqlstr7 = " and organization_id = "+paramValue;
			if(paramName.equals("organization_name")) sqlstr7 = " and organization_name like '%"+paramValue+"%'";
		}
		
		int totalSize = 0;

		try{
			opera = new DBOperation();
			opera.transcationBegin();
			String sql= "select count(*) as totalSize "
						+ "from complain "
						+ "where complain_id>0"+ sqlstr1 + sqlstr2 + sqlstr3 + sqlstr4 + sqlstr5+ sqlstr6 +sqlstr7+ sqlstr8 + sqlstr9+ sqlstr10 ;
			opera.prepStatment(sql);
			ResultSet res = opera.executeQuery();
			if (res.next()) {
				totalSize = res.getInt(1);
			}
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return totalSize;
	}
	
	public Complain query(int complain_id){
		Complain complain = null;
		try{
			opera = new DBOperation();
			opera.transcationBegin();
			
			String sql = "select "
				+ "complain_id,complain_name,complain_phone,organization_id,organization_name,complain_content," 
				+ "add_time,handle_user,handle_suggestion,hundle_time,ishandle,mobile,unit,address,email" 
				+ " from complain"
				+ " where complain_id=?";
			opera.prepStatment(sql);
			opera.setInt(1, complain_id);
			ResultSet res = opera.executeQuery();
			while(null!=res && res.next()){
				complain = new Complain();
				complain.setComplain_id(res.getInt(1));
				complain.setComplain_name(res.getString(2));
				complain.setComplain_phone(res.getString(3));
				complain.setOrganization_id(res.getInt(4));
				complain.setOrganization_name(res.getString(5));
				complain.setComplain_content(res.getString(6));

				complain.setAdd_time(res.getString(7));
				complain.setHandle_user(res.getString(8));
				complain.setHandle_suggestion(res.getString(9));
				complain.setHundle_time(res.getString(10));
				complain.setIshandle(res.getInt(11));
				complain.setMobile(res.getString(12));
				complain.setUnit(res.getString(13));
				complain.setAddress(res.getString(14));
				complain.setEmail(res.getString(15));
			}
		}catch(Exception e){
			log.error(e);
		}finally{
			opera.closePres();
			opera.closeConn();
		}
		return complain;
	}
	
	public boolean add(Complain complain){
    	boolean result = false;
    	try {
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "insert into complain(complain_name,complain_phone,organization_id,organization_name,complain_content,add_time,ishandle,mobile,unit,address,email)" 
				    + " values (?,?,?,?,?,SYSDATE(),?,?,?,?,?) ";
			opera.prepStatment(sql);
			opera.setString(1, complain.getComplain_name());
			opera.setString(2, complain.getComplain_phone());
			opera.setInt(3, complain.getOrganization_id());
			opera.setString(4, complain.getOrganization_name());
			opera.setString(5, complain.getComplain_content());
			opera.setInt(6, complain.getIshandle());
			opera.setString(7, complain.getMobile());
			opera.setString(8, complain.getUnit());
			opera.setString(9, complain.getAddress());
			opera.setString(10, complain.getEmail());
			
			opera.execute();
			result = opera.transcationCommit();
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return result;
    }
	
	/**
	 * 
	 * @param complain
	 * @return
	 */
	public boolean edit(Complain complain){
		boolean result = false;
		try{
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "update complain "							
					+ "set handle_user=?,handle_suggestion=?,hundle_time=SYSDATE(),ishandle=?" 
					+ " where complain_id=?";
			opera.prepStatment(sql);
			opera.setString(1, complain.getHandle_user());
			opera.setString(2, complain.getHandle_suggestion());
			opera.setInt(3, complain.getIshandle());
			opera.setInt(4, complain.getComplain_id());

			opera.executeUpdate();
			result = opera.transcationCommit();
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return result;
	}
	
	/**
	 * 
	 * @param complain_id
	 * @return
	 */
	public Boolean delete(int complain_id){
    	boolean result = false;
		try {
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "delete from complain "
					+ "where complain_id=? ";
			opera.prepStatment(sql);
			opera.setInt(1, complain_id);
			opera.execute();
			result = opera.transcationCommit();
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return result;
    	
    }
}